import xlrd
import pymysql
from z_xh_env import *
import z_xh_env
def process():
    book = xlrd.open_workbook(r"G:\xh\zz.xls")
    print("The number of worksheets is {0}".format(book.nsheets))
    print("Worksheet name(s): {0}".format(book.sheet_names()))
    sheetIndex = 0
    while sheetIndex <= 2:
        sh = book.sheet_by_index(sheetIndex)
        sheetIndex += 1
        print("{0} {1} {2}".format(sh.name, sh.nrows, sh.ncols))
        rowIndex = 0
        for rx in range(sh.nrows):
            sqlParent = f"select * from department where `Code` = '{sh.cell_value(rowx=rowIndex, colx=0)}'"
            parent = selectData(sqlParent)
            if(parent == None):
                print(
                    f'没有找到上级数据code:{sh.cell_value(rowx=rowIndex, colx=1)},name:{sh.cell_value(rowx=rowIndex, colx=2)},sql:{sqlParent}')
                continue
            fillPath = parent[6]+'/'+sh.cell_value(rowx=rowIndex, colx=2)
            sqlTemp = f" insert into Department (ParentId,Name,Code,Level,Sort,FullPath,CreatedTime,CreatedBy,ModifiedTime,ModifiedBy,Location_Lng,Location_Lat)VALUES({parent[0]},'{sh.cell_value(rowx=rowIndex, colx=2)}','{sh.cell_value(rowx=rowIndex, colx=1)}',0,0,'{fillPath}','2022-03-30 18:00:00','00000000-0000-0000-0000-000000000000','2022-03-30 18:00:00','00000000-0000-0000-0000-000000000000',0,0)"
            insertData(sqlTemp)
            # print(sh.row(rx))
            rowIndex += 1


def selectData(sql):
    try:
        db = pymysql.connect(host=DbHost, port=DbProt,
                             user=DbUser, passwd=DbPasswd, database=DbName)
        cursor = db.cursor()
        cursor.execute(sql)
        results = cursor.fetchall()
        print(len(results))
        if len(results) >= 1:
            return results[0]
        return None
    finally:
        cursor.close()
        db.close()


def insertData(sql):
    try:
        db = pymysql.connect(host=DbHost, port=DbProt,
                             user=DbUser, passwd=DbPasswd, database=DbName)
        cursor = db.cursor()
        cursor.execute(sql)
        db.commit()
    except Exception as e:
        print('保存数据异常', e)
    else:
        print('保存数据执行完成')
    finally:
        cursor.close()
        db.close()
        pass


def test():
    sql = " select * from Department where `Code` = '50002354' "
    with z_xh_env.buildDbHelper() as db:
        results=db.executeQuerySql(sql)
        #results = selectData(sql)
        if results and len(results)>=1:
            results = results[0]
        print(f'{results[0]}$$${results[6]}')
    # sqlParent = f"select * from department where `Code` = '{results[3]}'"
    # parent = selectData(sqlParent)
    # if(parent == None):
    #     print(f'没有找到上级数据{sqlParent}')
    # else:
    #     print(parent)

# process()

if __name__ == '__main__':
    test()
    pass
